package com.bears.god.sup.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bears.god.sup.model.Bill;
import com.bears.god.sup.util.DBConnection;

public class BillDAOImp implements IBillDAO {
	private static final String FIND_BY_ID = "select * from bill where id=?";
	
	private static final String FIND_BY_NAME = "select * from bill where vendor_name=?";
	
	private static final String FIND_ALL = "select * from bill";
	
	private static final String INSERT= "insert into bill"
			+ "(vendor_name,date,due_date) "
			+ "values(?,?,?)";
	private static final String UPDATE_PRICE= "update bill set "
			+ "bill_sum=?"
			+ " where id=?";

	private static final String UPDATE = "update bill set bill_pay=?,paid_flag=? where id=?";
			
	private static final String UPDATE_FLAG = "update bill set paid_flag=? where id=? ";

	private static final String DELETE= "DELETE FROM bill"
			+ " where id=?";

	@Override
	public void save(Bill bill) throws Exception {
		PreparedStatement prep = null;
		Connection conn =null;
		
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(INSERT);
			prep.setString(1, bill.getVendorName());
			prep.setString(2, bill.getDate());
			prep.setString(3, bill.getDueDate());
			
		
			prep.executeUpdate();
			ResultSet rst = prep.getGeneratedKeys();
			rst.next();
			int id = rst.getInt(1);
			bill.setId(id);
			
		} finally {
			DBConnection.close(prep, conn);
			}
	}
	
	
	@Override
	public void updateprice(Bill bill) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(UPDATE_PRICE);
			prep.setDouble(1, bill.getBillSum());
			prep.setInt(2, bill.getId());
			
			prep.executeUpdate();
		} finally {
			DBConnection.close(prep, conn);
		}
	}
	
	@Override
	public void update(Bill bill) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(UPDATE);
			prep.setString(1, bill.getBillPay());
			prep.setString(2, bill.getPaidFlag());
			prep.setInt(3, bill.getId());
			
			prep.executeUpdate();
		}finally{
			DBConnection.close(prep, conn);
		}
	}

	@Override
	public List<Bill>  findAll() throws Exception {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;

		List<Bill> bills = new ArrayList<Bill>();

		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_ALL);
			rs = prep.executeQuery();

			while (rs.next()) {
				Bill bill = new Bill();
				bill.setVendorName(rs.getString("vendor_name"));
				bill.setDate(rs.getString("date"));
				bill.setId(rs.getInt("id"));
				bill.setDueDate(rs.getString("due_date"));
				bill.setBillSum(rs.getDouble("bill_sum"));
				bill.setBillPay(rs.getString("bill_pay"));
				bill.setPaidFlag(rs.getString("paid_flag"));
				bills.add(bill);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(prep, conn);
		}
		return bills;
	}
	
	public List<Bill> findByName(String name) throws Exception{
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;


		List<Bill> billname = new ArrayList<Bill>();
		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_BY_NAME);
			prep.setString(1, name);
			rs = prep.executeQuery();

			while (rs.next()) {
				Bill bills = new Bill();
				bills.setVendorName(rs.getString("vendor_name"));
				bills.setDate(rs.getString("date"));
				bills.setId(rs.getInt("id"));
				bills.setDueDate(rs.getString("due_date"));
				bills.setBillSum(rs.getDouble("bill_sum"));
				bills.setBillPay(rs.getString("bill_pay"));
				bills.setPaidFlag(rs.getString("paid_flag"));
				billname.add(bills);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(prep, conn);
		}
		return billname;
	}
	public Bill findById(int id) throws Exception{
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rst = null;
		Bill bill = null;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(FIND_BY_ID);
			prep.setInt(1, id);
			
			rst = prep.executeQuery();
			if(rst.next()){
			 bill = new Bill();
			 bill.setVendorName(rst.getString("vendor_name"));
				bill.setDate(rst.getString("date"));
				bill.setId(rst.getInt("id"));
				bill.setDueDate(rst.getString("due_date"));
				bill.setBillSum(rst.getDouble("bill_sum"));
				bill.setBillPay(rst.getString("bill_pay"));
				bill.setPaidFlag(rst.getString("paid_flag"));
			}
		}finally{
			DBConnection.close(prep, conn);
		}
		return bill;
	}
	

	@Override
	public void detele(int id) throws Exception {
		Connection conn = null;
		PreparedStatement prep = null;

		try {
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(DELETE);
			prep.setInt(1, id);
		
			prep.executeUpdate();
			
			
		} finally {
			DBConnection.close(prep, conn);
			}
	}


}
